package com.litbw.dbbackend;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.litbw.dbbackend.DBManager.FieldType;
import com.litbw.event.Contest;
import com.litbw.event.Contest.Entry;
import com.litbw.user.User;

/**
 * The DBContestAdapter uses DBManager functionality to add, remove, update,
 * and search for Contest data within the database.
 * 
 * @author Michael Morris
 *
 */
public class DBContestAdapter extends AbstractDBAdapter {

	/**
	 * The ONE instance of the DBEventAdapter.
	 */
	private static DBContestAdapter _instance = null;
	
	protected DBContestAdapter() {
		super("contests");
		
		// Create field setup for table
		Map<String, FieldType> fields = new HashMap<String, FieldType>();
		fields.put("id", FieldType.INTEGER);
		fields.put("eventparentid", FieldType.INTEGER);
		fields.put("description", FieldType.TEXT);
		fields.put("rules", FieldType.TEXT);
		fields.put("judges", FieldType.TEXT);
		
		super.setTableFields(fields);
		super.setAutoIncrement(false);
		super.setPrimary("id");
		
	}
	
	/**
	 * Factory model... Ensures there is ONLY one DBEventAdapter present at
	 * any given time.
	 */
	private static void checkAdapter() {
		
		// Make sure the DBManager has an instance.
		if (_instance == null)
			_instance = new DBContestAdapter();
	
	}

	/**
	 * Retrieves a field/value pair list from the specified Contest.
	 * @param contest The contest object to build list of.
	 * @return A String containing the comma separated field/value pair
	 * list.
	 */
	private static String fieldValuePairsFromContest(final Contest contest) {
		
		String rtn = null;
		
		if (contest != null) {
			
			final StringBuilder sb = new StringBuilder();
			//(id, location, date, time, description)
			sb.append("id=");
			sb.append(sqlAcceptable(contest.getId()));
			sb.append(", ");
			sb.append("eventparentid=");
			sb.append(sqlAcceptable(contest.getEventParentId()));
			sb.append(", ");
			sb.append("description=");
			sb.append(sqlAcceptable(contest.getDescription()));
			sb.append(", ");
			sb.append("rules='");
			// Build the rules string.
			sb.append(buildRulesList(contest));
			sb.append("', ");
			sb.append("judges='");
			// Build judges list.
			sb.append(buildJudgeList(contest));
			sb.append("'");
			
			rtn = sb.toString();
			
		}
		
		return rtn;
		
	}
	
	/**
	 * Build a, string, list of rules for use in an SQL statement.
	 * @param contest The contest to retrieve the rules from.
	 * @return A string containing all the rules separated by semi-colons.
	 */
	private static String buildRulesList(final Contest contest) {
		
		String rtn = "";
		
		if (contest != null && !contest.getCategories().isEmpty()) {
			
			final StringBuilder sb = new StringBuilder();
			
			final String[] rules = 
					contest.getCategories().toArray(new String[0]);
			
			for (int i = 0; i < rules.length; i++) {
				if (i > 0)
					sb.append(";");
				sb.append(rules[i]);
			}
			
			rtn = sb.toString();
			
		}
		
		return rtn;
		
	}
	
	/**
	 * Build a, String, list of judges username/emails for use in an SQL statement.
	 * @param contest The contest to retrieve judges username from.
	 * @return A string which contains all the judges names separated by
	 * semi-colons.
	 */
	private static String buildJudgeList(final Contest contest) {
		
		String rtn = "";
		
		if (contest != null && !contest.getRegistrants().isEmpty()) {
			
			final StringBuilder sb = new StringBuilder();
			
			final User[] judges = contest.getRegistrants().toArray(new User[0]);
			
			for (int i = 0; i < judges.length; i++) {
				if (i > 0)
					sb.append(";");
				sb.append(judges[i].getEmail());
			}
			
			rtn = sb.toString();
			
		}
		
		return rtn;
		
	}

	/**
	 * Retrieve a value list from the specified contest.
	 * @param contest The user object to build list of.
	 * @return A String containing the comma separated value list.
	 */
	private static String valueListFromContest(final Contest contest) {
		
		String rtn = null;
		
		if (contest != null) {
			
			final StringBuilder sb = new StringBuilder();
			
			sb.append(sqlAcceptable(contest.getId()));
			sb.append(", ");
			sb.append(sqlAcceptable(contest.getEventParentId()));
			sb.append(", ");
			sb.append(sqlAcceptable(contest.getDescription()));
			sb.append(", ");
			sb.append(sqlAcceptable(buildRulesList(contest)));
			sb.append(", ");
			sb.append(sqlAcceptable(buildJudgeList(contest)));
			
			rtn = sb.toString();
			
		}
		
		return rtn;
		
	}
	
	/**
	 * Add the given contest to the Contest table of the database.
	 * @param contest The contest object to insert.
	 * @return True if addition was successful, false otherwise.
	 */
	public static boolean addContest(final Contest contest) {
		
		boolean rtn = false;
		
		checkAdapter();
		
		if (_instance.checkDatabaseConnection() && contest != null) {
			
			// Build the sql statement.
			final String sql = "INSERT INTO " + _instance.table_name  +
					" (id, eventparentid, description, rules, judges) values (" +
					   valueListFromContest(contest) + ")";
			
			// Run the statement
			if (_instance.db.runUpdate(sql) > 0)
				rtn = true;
			
			// Disconnect from database and change return status.
			_instance.disconnect();
			
		}
		
		return rtn;
		
	}
	
	/**
	 * Delete the contest with the given contest id, from the Contest table.
	 * @param id The contest id to delete.
	 * @return True if the contest was removed, false otherwise.
	 */
	public static boolean deleteContest(final int id) {
		
		boolean rtn = false;
		
		checkAdapter();
		
		if (_instance.checkDatabaseConnection()) {
			
			// Build the sql statement.
			final String sql = "DELETE FROM " + _instance.table_name  +
					" WHERE id=" + id;

			// Run the statement
			if (_instance.db.runUpdate(sql) > 0)
				rtn = true;
			
			// Disconnect from database and change return status.
			_instance.disconnect();
			
		}
		
		return rtn;
		
	}
	
	/**
	 * Update the contest with contest.id with the data provided within
	 * the contest object.
	 * @param contest The contest object with desired id and updated fields.
	 * @return True if update successful, false otherwise.
	 */
	public static boolean updateContest(final Contest contest) {
		
		boolean rtn = false;
		
		checkAdapter();
		
		if (_instance.checkDatabaseConnection() && contest != null) {
			
			// Build the sql statement.
			final String sql = "UPDATE " + _instance.table_name  +
					" SET " + fieldValuePairsFromContest(contest) +
					" WHERE id=" + contest.getId();
			
			// Run the statement
			if (_instance.db.runUpdate(sql) > 0)
				rtn = true;
			
			// Disconnect from database and change return status.
			_instance.disconnect();
			
		}
		
		return rtn;

	}
	
	/**
	 * Retrieve the contest with the given Contest id.
	 * @param id The contest id to search for.
	 * @return A Contest object, or null if not found.
	 */
	public static Contest getContest(final int id) {
		
		Contest rtn = null;
		
		checkAdapter();
		
		if (_instance.checkDatabaseConnection()) {
			
			// Build the sql statement.
			final String sql = "SELECT * FROM " + _instance.table_name +
					" WHERE id=" + id;
			
			final ResultSet rslt = _instance.db.runQuery(sql);
			
			final Contest[] all = buildContestFromResult(rslt);
			if (all != null)
				rtn = all[0];
			
			// Disconnect from database and change return status.
			_instance.disconnect();
			
		}
		
		return rtn;

	}
	
	/**
	 * Retrieve the contest with the given Contest id.
	 * @param id The contest id to search for.
	 * @return A Contest object, or null if not found.
	 */
	public static Contest[] getContestsByJudge(final String judgeEmail) {
		
		Contest[] rtn = null;
		
		checkAdapter();
		
		if (_instance.checkDatabaseConnection()) {
			
			// Build the sql statement.
			final String sql = "SELECT * FROM " + _instance.table_name +
					" WHERE judges LIKE '" + judgeEmail + "' OR judges LIKE '%;"
					+ judgeEmail + "' OR judges LIKE '" + judgeEmail +
					";%' OR judges LIKE '%;" + judgeEmail + ";%'";
			
			final ResultSet rslt = _instance.db.runQuery(sql);
			
			rtn = buildContestFromResult(rslt);
			
			// Disconnect from database and change return status.
			_instance.disconnect();
			
		}
		
		return rtn;

	}
	
	
	/**
	 * Retrieve all contests within the Contest table.
	 * @return An array of Contest objects, or null if none exist in the table.
	 */
	public static Contest[] getAllContests() {
		
		Contest[] rtn = null;
		
		checkAdapter();
		
		if (_instance.checkDatabaseConnection()) {
			
			// Build the sql statement.
			final String sql = "SELECT * FROM " + _instance.table_name;
			
			final ResultSet rslt = _instance.db.runQuery(sql);
			
			rtn = buildContestFromResult(rslt);
			
			// Disconnect from database and change return status.
			_instance.disconnect();
			
		}
		
		return rtn;
		
	}
	
	/**
	 * Retrieve an array of contests which have a specific parent event id.
	 * @param eventid The parent Event id to search for.
	 * @return An array of Contest objects, or null.
	 */
	public static Contest[] getContestsForEvent(final int eventid) {
		
		Contest[] rtn = null;
		
		checkAdapter();
		
		if (_instance.checkDatabaseConnection()) {
			
			// Build the sql statement.
			final String sql = "SELECT * FROM " + _instance.table_name +
					" WHERE eventparentid=" + eventid;
			
			final ResultSet rslt = _instance.db.runQuery(sql);
			
			rtn = buildContestFromResult(rslt);
			
			// Disconnect from database and change return status.
			_instance.disconnect();
			
		}
		
		return rtn;
		
	}
	
	/**
	 * Build Contest objects based on the results of a query ResultSet.
	 * @param set The ResultSet passed back from database query.
	 * @return An array of Contest objects or null.
	 */
	private static Contest[] buildContestFromResult(final ResultSet set) {
		
		Contest[] contests = null;
		
		if (set != null) {
			
			final List<Contest> retrieved = new ArrayList<Contest>();
			
			try {
				
				while (set.next()) {
					
					// This try/catch block ensures that ONLY those contests
					// which cannot be built are skipped.
					try {
						
						// Gather data stored directly inside the Contest table.
						Contest current = new Contest();
						current.setId( set.getInt("id") );
						current.setDescription( set.getString("description") );
						current.setEventParentId( set.getInt("eventparentid") );
						
						final String judges[] = set.getString("judges")
												 .split(";");
						for (String judge_username : judges) {
							
							final User usr = DBUserAdapter.getUser(judge_username);
							
							if (usr == null)
								System.err.println("could not find judge[" + judge_username + "] in User table!");
							else
								current.addJudge(usr);
							
						}
						
						final String rules[] = set.getString("rules")
												 .split(";");
						for (String rule : rules)
							current.addRule(rule);
						
						Entry[] entries = DBEntryAdapter.getEntriesForContest(current.getId());
						if (entries != null)
							for (Entry e : entries)
								current.addEntry(e);
						
						retrieved.add(current);
						
					} catch (SQLException sqle) {
						System.err.println("skipping one or more contest results: " + sqle.getMessage());
					}
					
					
				}
				
			} catch (SQLException sqle) {
				System.err.println("cannot parse result set: " + sqle.getMessage());
			}
						
			if (retrieved != null && !retrieved.isEmpty())
				contests = retrieved.toArray(new Contest[0]);
			
		}
		
		return contests;
		
	}

}
